package com.infoblazer.jimql;

import org.apache.log4j.Logger;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * User: Dave
 * Date: Nov 14, 2010
 * Time: 11:00:36 AM
 */
public abstract class Jimql {

    private static final Logger LOGGER = Logger.getLogger(Jimql.class.getName());

    protected static final String SPACE = " ";
    protected long batchSize = 0;

    protected Connection conn;
    protected static ThreadLocal<Connection> connectionThreadLocal = new ThreadLocal<Connection>();


    protected static final String HASHCODE_COL = "JIMQL_HASHCODE";

    protected abstract Map<String, Map<Integer, Object>> getHashCodes();

    protected DBTYPE dbType = DBTYPE.HSQLDB;

    private JimqlConnection jimqlConnection = null;

    protected Jimql() {

        buildConnection();
    }

    protected Jimql(DBTYPE dbType) {
        this.dbType = dbType;
        buildConnection();
    }


    protected void buildConnection() {
        jimqlConnection = ConnectionTypeFactory.buildJimqlConnection(dbType);
        conn = jimqlConnection.buildConnection(connectionThreadLocal);
    }

    public enum DBTYPE {
        H2, HSQLDB, Derby
    }

    protected class Relation {
        private String name;

        private List<Column> columns = new ArrayList<Column>();


        public List<Column> getColumns() {
            return columns;
        }

        public void setColumns(List<Column> columns) {
            this.columns = columns;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }


    }

    protected class Column {
        protected String columnName;
        private Method getter;

        private Class<?> methodType;

        public Class<?> getMethodType() {
            return methodType;
        }

        public void setMethodType(Class<?> methodType) {
            this.methodType = methodType;
        }

        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public Method getGetter() {
            return getter;
        }

        public void setGetter(Method getter) {
            this.getter = getter;
        }


        public boolean isInteger() {

            return methodType.getName().equals("java.lang.Integer") ||
                    methodType.getName().equals("int") ||
                    methodType.getName().equals("long") ||
                    methodType.getName().equals("java.lang.Long");
        }

        public boolean isString() {
            return methodType.getName().equals("java.lang.String");
        }

        public boolean isBoolean() {
            return methodType.getName().equals("java.lang.Boolean")
                    || methodType.getName().equals("booleant");
        }

        public boolean isNumeric() {
            return methodType.getName().equals("java.lang.Double") ||
                    methodType.getName().equals("java.lang.Float") ||
                    methodType.getName().equals("double") ||
                    methodType.getName().equals("float") ||
                    methodType.getName().equals("java.math.BigDecimal")
                    ;

        }

        public boolean isDate() {
            return methodType.getName().equals("java.util.Date") ||
                    methodType.getName().equals("java.sql.Timestamp");
        }

        public boolean isSupportedType() {
            return isString() || isInteger() || isNumeric() || isDate() || isBoolean();
        }

        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;

            Column column = (Column) o;

            if (!columnName.equals(column.columnName)) return false;

            return true;
        }

        @Override
        public int hashCode() {
            return columnName.hashCode();
        }
    }


    public void shutDown() {
        try {
            conn.close();


        } catch (SQLException e) {
            LOGGER.error("An error occurred.", e);
        }
    }


    protected void createIndexes(List<Relation> relations, String criteria) {
        createIndexes(relations, criteria, "");


    }

    public void createIndexes(String tableName, String... columns) {

        try {
            Statement st = conn.createStatement();

            for (String column : columns) {

                StringBuilder sb = new StringBuilder();
                sb.append("CREATE INDEX ");
                sb.append(tableName.toUpperCase()).append('_').append(column.toUpperCase());
                sb.append(" ON ").append(tableName).append('(');
                sb.append(column).append("  ASC)");
                st.execute(sb.toString());
            }


        } catch (SQLException e) {
            if (!e.getMessage().contains("object name already exists")) {
                LOGGER.error(e);
            }
        }

    }


    protected void createIndexes(List<Relation> relations, String criteria, String groupBy) {
        //fudge this for now , maybe include all fields specified in join zipcode.city = city.id
        try {
            Statement st = conn.createStatement();

            for (Relation relation : relations) {
                for (Column column : relation.getColumns()) {
                    if (criteria.toUpperCase().contains(relation.getName().toUpperCase() + '.' + column.getColumnName().toUpperCase())
                            || groupBy.toUpperCase().contains(column.getColumnName().toUpperCase())) {
                        StringBuilder sb = new StringBuilder();
                        sb.append("CREATE INDEX ");
                        sb.append(relation.getName().toUpperCase()).append('_').append(column.getColumnName().toUpperCase());
                        sb.append(" ON ").append(relation.getName()).append('(');
                        sb.append(column.getColumnName()).append("  ASC)");
                        st.execute(sb.toString());
                    }

                }
            }
        } catch (SQLException e) {
            if (!e.getMessage().contains("object name already exists")) {
                LOGGER.error(e);
            }
        }
    }

    protected void createTable(Relation relation) {

        try {
            Statement st = conn.createStatement();

            dropIfExists(relation.getName());
            if (getHashCodes() != null) {
                getHashCodes().put(relation.getName(), null);
            }

            StringBuilder sb = new StringBuilder();
            sb.append("CREATE TABLE ");
            sb.append(relation.getName()).append("( ").append(relation.getName()).append('_' + HASHCODE_COL + " BIGINT,");

            for (Column column : relation.getColumns()) {
                sb.append(column.getColumnName()).append(SPACE);
                sb.append(determineSqlType(column));
                sb.append(", ");


            }
            sb.append("PRIMARY KEY ( ").append(relation.getName()).append('_' + HASHCODE_COL + ')');
            sb.append(')');

            st.execute(sb.toString());


        } catch (SQLException e) {
            LOGGER.error(e);
        } finally {

        }


    }


    protected String determineSqlType(Column column) {

        if (column.isInteger()) {
            return "BIGINT";
        } else if (column.isString()) {
            return "VARCHAR(1000)";
        } else if (column.isNumeric()) {
            return "FLOAT";
        } else if (column.isBoolean()) {
            return "BOOLEAN";
        } else if (column.isDate()) {
            return "DATETIME";
        }

        //TODO should throw exception here
        return null;
    }


    public long getBatchSize() {
        return batchSize;
    }

    public void setBatchSize(long batchSize) {
        this.batchSize = batchSize;
    }


    protected void dropIfExists(String name) throws SQLException {
        jimqlConnection.dropIfExists(conn, name);
    }

    public void loadData(NamedList... dataLists) {
        List<Relation> relations = findRelations(dataLists);
        int i = 0;
        for (Relation relation : relations) {
            createTable(relation);
            loadData(dataLists[i].getDataList(), relation);
            i++;
        }

    }

    public void loadData(boolean noRetrieval, List... dataLists) {
        List<Relation> relations = findRelations(dataLists);
        int i = 0;
        for (Relation relation : relations) {
            createTable(relation);
            loadData(dataLists[i], relation, noRetrieval);
            i++;
        }

    }

    public void loadData(List... dataLists) {
        loadData(false, dataLists);

    }


    protected List<Relation> findRelations(NamedList[] dataLists) {
        List<Relation> relations = new ArrayList<Relation>();
        //need to create a hashlist to see if we need to rename lists.
        Map<String, Integer> relationMap = new HashMap<String, Integer>();


        for (NamedList dataList : dataLists) {

            Relation relation = findColumns(dataList.getListName(), dataList.getDataList());
            relations.add(relation);

        }
        return relations;
    }

    protected List<Relation> findRelations(List[] dataLists) {

        Map<String, Integer> relationMap = new HashMap<String, Integer>();

        NamedList namedLists[] = new NamedList[dataLists.length];


        String className;
        int i = 0;
        for (List dataList : dataLists) {
            try {
                if (dataList.get(0) instanceof Map) {
                    className = "tempmap" + Math.abs(dataList.get(0).hashCode());
                } else {
                    BeanInfo beanInfo = Introspector.getBeanInfo(dataList.get(0).getClass());
                    className = beanInfo.getBeanDescriptor().getName();
                    Integer classCount = relationMap.get(className);
                    if (classCount == null) {
                        relationMap.put(className, 1);
                        classCount = 1;
                    } else {
                        relationMap.put(className, classCount + 1);
                        classCount++;
                    }


                    if (classCount > 1) {
                        className = className + classCount;
                    }
                }
                namedLists[i] = new NamedList(className, dataList);

                i++;


            } catch (IntrospectionException e) {
                LOGGER.error(e);
            }
        }
        return findRelations(namedLists);
    }


    protected abstract Relation findColumns(String className, List dataList);

    protected void loadData(List tuples, Relation relation) {
        loadData(tuples, relation, false);
    }

    protected void loadData(List tuples, Relation relation, boolean noRetrieval) {
        Map<Integer, Object> map = null;
        if (!noRetrieval) {
            map = new HashMap<Integer, Object>();
        }
        PreparedStatement ps = null;
        long startTime = 0;
        long endTime = 0;
        if (LOGGER.isTraceEnabled()) {
            startTime = System.currentTimeMillis();
        }

        try {
            StringBuilder placeholders = new StringBuilder("?,");
            StringBuilder sb = new StringBuilder();
            sb.append("insert into ").append(relation.getName()).append(" (").append(relation.getName()).append('_' + HASHCODE_COL + ',');
            int i = 1;
            for (Column column : relation.getColumns()) {
                sb.append(column.getColumnName()).append(SPACE);
                placeholders.append('?');
                if (i < relation.getColumns().size()) {
                    sb.append(", ");
                    placeholders.append(',');
                }
                i++;
            }

            sb.append(')');
            sb.append(" values(").append(placeholders).append(')');

            ps = conn.prepareStatement(sb.toString());
            int id = 0;
            for (Object tuple : tuples) {
                id++;
                if (!noRetrieval) {
                    map.put(id, tuple);
                }
                i = 1;
                ps.setInt(i, id);
                for (Column column : relation.getColumns()) {
                    i = loadColumnValue(ps, i, tuple, column);
                }
                ps.addBatch();
                if (batchSize != 0 && id % batchSize == 0) {
                    ps.executeBatch();
                }
            }
            if (batchSize == 0 || id % batchSize != 0) {
                ps.executeBatch();
            }

        } catch (SQLException e) {
            LOGGER.error(e);
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                LOGGER.error(e);
            }
        }
        if (getHashCodes() != null && !noRetrieval) {
            getHashCodes().put(relation.getName(), map);
        }
        if (LOGGER.isTraceEnabled()) {
            endTime = System.currentTimeMillis();
            LOGGER.trace("Loaded " + tuples.size() + " tuples in " + (endTime - startTime) + " ms.");
        }
    }

    protected abstract int loadColumnValue(PreparedStatement ps, int i, Object tuple, Column column) throws SQLException;


    protected int loadColumnValueObject(PreparedStatement ps, int i, Column column, Object value) throws SQLException {
        int inc = i;

        if (column.isInteger()) {
            inc++;
            if (value == null) {
                ps.setNull(inc, Types.BIGINT);
            } else {
                Long longValue = Long.valueOf(value.toString());
                ps.setLong(inc, longValue);
            }
        } else if (column.isString()) {
            inc++;
            String stringValue = (String) value;
            if (stringValue == null) {
                ps.setNull(inc, Types.VARCHAR);
            } else {
                ps.setString(inc, stringValue);
            }

        } else if (column.isDate()) {
            inc++;

            if (value == null) {
                ps.setNull(inc, Types.DATE);
            } else {
                java.util.Date dateValue = (java.util.Date) value;
                ps.setDate(inc, new java.sql.Date(dateValue.getTime()));


            }

        } else if (column.isNumeric()) {
            inc++;
            Double numericValue;

            if (value == null) {
                ps.setNull(inc, Types.FLOAT);
            } else {
                numericValue = (Double) value;
                ps.setDouble(inc, numericValue);
            }

        } else if (column.isBoolean()) {
            inc++;
            Boolean booleanValue;

            if (value == null) {
                ps.setNull(inc, Types.BOOLEAN);
            } else {
                booleanValue = (Boolean) value;
                ps.setBoolean(inc, booleanValue);
            }

        }
        return inc;
    }


}
